/*
 * 文 件 名:  SqlGenerate.java
 * 版    权:  Chrhc Technologies Co., Ltd. Copyright 2015-2099,  All rights reserved
 * 描    述:  <描述>
 * 修 改 人: 姓名 xxx
 * 修改时间:  2015年12月8日
 */
package org.mytool.common.db.sql;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.mytool.common.db.ColumnData;
import org.mytool.common.db.DbWhere;

/**
 * sql 生成逻辑
 * 
 * @author 姓名 wangchen
 * @version [1.0, 2015年12月8日]
 */
public class SqlGenerate
{
    /**
     * 全插入，如果没有参数则插入null
     * 
     * @param tableName
     * @param columnDatas
     * @return
     */
    public static String getInsertSql(String tableName, List<ColumnData> columnDatas, Map param)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("insert  into ");
        sb.append(tableName);
        sb.append("(");
        String colName = null;
        for (ColumnData col : columnDatas)
        {
            if (i > 0)
            {
                sb.append(",");
            }
            sb.append(col.getColumnName());
            i++;
        }
        sb.append(")");
        sb.append(" VALUES (");
        Object val = null;
        i = 0;
        ParameterBean bean;
        for (ColumnData col : columnDatas)
        {
            colName = col.getColumnName();
            //val = param.get(colName);
            bean = getMap(colName,param);
            val = null;
            if(bean!=null){
            	val = bean.getVal();
            }
            if (i > 0)
            {
                sb.append(",");
            }
            if (val == null)
            {
                sb.append("null");
            }
            else
            {
                sb.append(":");
                //sb.append(col.getColumnName());
                sb.append(bean.getKey());
            }
            
            i++;
        }
        sb.append(")");
        return sb.toString();
    }
    
    /**
     * 不为空d插入
     * 
     * @param tableName
     * @param columnDatas
     * @return
     */
    public static String getInsertSelectiveSql(String tableName, List<ColumnData> columnDatas, Map param)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("insert  into ");
        sb.append(tableName);
        sb.append("(");
        String colName = null;
        Object val = null;
        ParameterBean bean;
        for (ColumnData col : columnDatas)
        {
        	 colName = col.getColumnName();
             //val = param.get(colName);
             bean = getMap(colName,param);
             val = null;
             if(bean!=null){
             	val = bean.getVal();
             }
            
             if (val == null)
             {
                 continue;
             }
             
             
            if (i > 0)
            {
                sb.append(",");
            }
            sb.append(col.getColumnName());
            i++;
        }
        sb.append(")");
        sb.append(" VALUES (");
       
        i = 0;
        for (ColumnData col : columnDatas)
        {
            colName = col.getColumnName();
            //val = param.get(colName);
            bean = getMap(colName,param);
            val = null;
            if(bean!=null){
            	val = bean.getVal();
            }
            
            if (val == null)
            {
            	 continue;
            }
            
            if (i > 0)
            {
                sb.append(",");
            }
            
            sb.append(":");
            //sb.append(col.getColumnName());
            sb.append(bean.getKey());
            i++;
        }
        sb.append(")");
        return sb.toString();
    }
    
    
    /**
     * 全更新，如果没有参数则插入null
     * 
     * @param tableName
     * @param columnDatas
     * @return
     */
    public static String getUpdateByPrimaryKeySql(String tableName, List<ColumnData> columnDatas, Map param)
    {
        int i = 0;
        List<String> pris = new ArrayList();
        StringBuffer sb = new StringBuffer("update  ");
        sb.append(tableName);
        sb.append(" set ");
        String colName = null;
        ParameterBean bean;
        for (ColumnData col : columnDatas)
        {
        	colName = col.getColumnName();
        	//if(colName.equals("id")){
        	if(col.isIfPrimKey()){
        		pris.add(colName);
        		continue;
        	}
            if (i > 0)
            {
                sb.append(",");
            }
            
            sb.append(colName);
            
            bean = getMap(colName,param);
            if(bean!=null && bean.getVal()!=null){//sure parameter same
            	sb.append("=:");
            	colName = bean.getKey();
            	sb.append(colName);
            }else{
            	sb.append("= null ");
            }
            
            i++;
        }
        sb.append(" WHERE ");
        i=0;
        for(String key :pris){
        	if(i>0){
        		sb.append(" and ");
        	}
        	sb.append(key);
        	sb.append(" = :");
        	sb.append(key);
        	i++;
        }
        //sb.append(" id = :id");
        return sb.toString();
    }
    
    /**
     * 全更新，如果没有参数则插入null
     * 
     * @param tableName
     * @param columnDatas
     * @return
     */
    public static String getUpdateByWhereSql(String tableName, List<ColumnData> columnDatas, Map param, DbWhere where)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("update  ");
        sb.append(tableName);
        sb.append(" set ");
        String colName = null;
        ParameterBean bean;
        for (ColumnData col : columnDatas)
        {
        	colName = col.getColumnName();
        	//if(colName.equals("id")){
        	if(col.isIfPrimKey()){
        		continue;
        	}
            if (i > 0)
            {
                sb.append(",");
            }
            sb.append(colName);
            
            bean = getMap(colName,param);
            if(bean!=null && bean.getVal()!=null){//sure parameter same
            	sb.append("=:");
            	colName = bean.getKey();
            	sb.append(colName);
            }else{
            	sb.append("= null ");
            }
            i++;
        }
        sb.append(where.toWhereString());
        return sb.toString();
    }
    
    /**
     * 更新不为空的值
     * 
     * @param tableName
     * @param columnDatas
     * @return
     */
    public static String getUpdateByPrimaryKeySelectiveSql(String tableName, List<ColumnData> columnDatas, Map param)
    {
        int i = 0;
        List<String> pris = new ArrayList();
        StringBuffer sb = new StringBuffer("update  ");
        sb.append(tableName);
        sb.append(" set ");
        String colName = null;
        Object val = null;
        ParameterBean bean;
        for (ColumnData col : columnDatas)
        {
        	colName = col.getColumnName();
        	val = null;
            bean = getMap(colName,param);
            if(bean!=null ){//sure parameter same
            	val = bean.getVal();
            }
          //if(colName.equals("id")){
        	if(col.isIfPrimKey()){
        		pris.add(colName);
        		continue;
        	}
            if(val==null){
            	continue;
            }
            if (i > 0)
            {
                sb.append(",");
            }
            sb.append(colName);
            sb.append("=:");
            sb.append(bean.getKey());
            i++;
        }
        sb.append(" WHERE ");
        i=0;
        for(String key :pris){
        	if(i>0){
        		sb.append(" and ");
        	}
        	sb.append(key);
        	sb.append(" = :");
        	sb.append(key);
        	i++;
        }
        //sb.append(" id = :id");
        return sb.toString();
    }
    
    /**
     * 更新不为空的值
     * 
     * @param tableName
     * @param columnDatas
     * @return
     */
    public static String getUpdateByWhereSelectiveSql(String tableName, List<ColumnData> columnDatas, Map param, DbWhere where)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("update  ");
        sb.append(tableName);
        sb.append(" set ");
        String colName = null;
        Object val = null;
        ParameterBean bean;
        for (ColumnData col : columnDatas)
        {
        	colName = col.getColumnName();
           // val = param.get(colName);
        	val = null;
            bean = getMap(colName,param);
            if(bean!=null ){//sure parameter same
            	val = bean.getVal();
            }
            //if(colName.equals("id")){
            if(col.isIfPrimKey()){
        		continue;
        	}
            if(val==null){
            	continue;
            }
            if (i > 0)
            {
                sb.append(",");
            }
            sb.append(colName);
            sb.append("=:");
            sb.append(bean.getKey());
            i++;
        }
        sb.append(where.toWhereString());
        return sb.toString();
    }
    
    
    
    /**
     * 按照主键删除
     * 
     * @param tableName
     * @param priKey
     * @return
     */
    public static String getDeleteByPrimaryKeySql(String tableName,String priKey, String id)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("delete from ");
        sb.append(tableName);
       
        sb.append(" WHERE ");
        sb.append(priKey).append("  = '").append(id).append("' ");
        return sb.toString();
    }
    
    
    
    /**
     * 更新不为空的值
     * 
     * @param tableName
     * @param where
     * @return
     */
    public static String getDeleteSqlByWhere(String tableName, DbWhere where)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("delete from ");
        sb.append(tableName);
        sb.append(where.toWhereString());
        return sb.toString();
    }
    
    
    public static String getSelectSqlByWhere(String tableName, DbWhere where)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("select * from ");
        sb.append(tableName);
        sb.append(where.toWhereString());
        String orderby = where.getOrderByClause();
        if(orderby!=null && !orderby.equals("")){
            orderby = orderby.toLowerCase();
            int orderIndex = orderby.indexOf("order");
            int byIndex = orderby.indexOf("by");
            if(orderIndex>-1 && orderIndex<byIndex){
                sb.append(orderby);
            }else{
                sb.append(" order by ");
                sb.append(orderby);
            }
        }
        return sb.toString();
    }
    
    public static String getCountSqlByWhere(String tableName, DbWhere where)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("select count(1) sl from ");
        sb.append(tableName);
        sb.append(where.toWhereString());
        return sb.toString();
    }
    
    public static String getSelectSqlByPrimaryKey(String tableName,String priKey, String id)
    {
        int i = 0;
        StringBuffer sb = new StringBuffer("select * from ");
        sb.append(tableName);
        sb.append(" where ");
        sb.append(priKey).append("  = '").append(id).append("' ");;
        return sb.toString();
    }
    
    /**
     * According to the rules of the hump
     * @param columnName
     * @param map
     * @return
     */
    private static ParameterBean getMap(String columnName,Map map){
    	ParameterBean bean = new ParameterBean();
    	Object obj = map.get(columnName);
    	if(obj!=null){
    		bean.setKey(columnName);
    		bean.setVal(obj);
    		return bean;
    	}
    	columnName = reName(columnName);
    	obj =  map.get(columnName);
    	if(obj!=null){
    		bean.setKey(columnName);
    		bean.setVal(obj);
    		return bean;
    	}
    	return null;
    }
    
    private static String reName(String name)
    {
        String arr[] = name.split("_");
        int i = 0;
        StringBuffer buf = new StringBuffer();
        for (String s : arr)
        {
            if (i > 0)
            {
                buf.append(s.substring(0, 1).toUpperCase() + s.substring(1));
            }
            else
            {
                buf.append(s.substring(0, 1).toLowerCase() + s.substring(1));
            }
            i++;
        }
        return buf.toString();
    }
    
    /**
     * <一句话功能简述> <功能详细描述>
     * 
     * @param args
     */
    public static void main(String[] args)
    {
        // TODO Auto-generated method stub
        String s = "Log_name_age";
        s = reName(s);
        System.out.println(s);
    }
    
}
